from flask import Flask, request, redirect, url_for, abort, make_response, json, jsonify \
    , session, abort, render_template, Markup, globals, flash, json
from urllib.parse import urlparse, urljoin
app = Flask(__name__)
app.secret_key = "secret string"
from flask_sqlalchemy import SQLAlchemy
# from flask_wtf import FlaskForm
# from wtforms import StringField, SubmitField
# from wtforms.validators import DataRequired, Required
import datetime
import sqlalchemy
import traceback
from sqlalchemy import create_engine
from sqlalchemy.sql import text, func
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
import pymysql
from sqlalchemy import create_engine
# from flask_script import Manager,prompt_bool
# // 链接数据库
from datetime import timedelta
import traceback
import os
import pymysql
from werkzeug.security import generate_password_hash #加密
app = Flask(__name__, static_folder='', static_url_path='')
app.config['SECRET_KEY'] = os.urandom(24)
app.config['PERMANENT_SESSION_LIFETIME'] = timedelta(days=7) #session过期时间
# // 链接数据库
pymysql.install_as_MySQLdb()
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:qq2361373869@127.0.0.1/my_god'
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
db = SQLAlchemy(app)
from sqlalchemy import Column,Integer,String,ForeignKey,Table

#学生跟学科多对多
subjects_students = db.Table(
    "subjects_students",
    db.Column("student_id", db.Integer, db.ForeignKey("student.id")),
    db.Column("subject_id", db.Integer, db.ForeignKey("subject.id"))
)

#学生跟课程多对多
courses_students = db.Table(
    "courses_students",
    db.Column("student_id", db.Integer, db.ForeignKey("student.id")),
    db.Column("course_id", db.Integer, db.ForeignKey("course.id"))
)
Base = sqlalchemy.ext.declarative.declarative_base()
# 定义数据库的学生模型
class student(db.Model):
    __tablename__ = "student"
    id = db.Column(db.Integer,primary_key=True)
    name = db.Column(db.String(64))
    sex = db.Column(db.String(64))
    email = db.Column(db.String(120))
    phone = db.Column(db.String(64))
    students = db.relationship('subject', secondary=subjects_students, back_populates='subjects')
    students1 = db.relationship('course', secondary=courses_students, back_populates='courses')

    # 将查询对象转换成字典
    def to_dict(self):
        return {c.name: getattr(self, c.name, None) for c in self.__table__.columns}

    Base.to_dict = to_dict

    # 查询多个对象转换成字典
    def to_json(all_vendors):
        v = [ven.to_dict() for ven in all_vendors]
        return v


# 定义学科类
class subject(db.Model):
    __tablename__ = "subject"
    id = Column(db.Integer,primary_key=True)
    sub = Column(db.String(32))
    teacher =Column(db.String(32))
    chongxiu = Column(db.String(32))
    chengji1 = Column(db.Integer)
    subjects = db.relationship('student', secondary=subjects_students, back_populates='students')

    # 将查询对象转换成字典
    def to_dict(self):
        return {c.name: getattr(self, c.name, None) for c in self.__table__.columns}

    Base.to_dict = to_dict

    # 查询多个对象转换成字典
    def to_json(all_vendors):
        v = [ven.to_dict() for ven in all_vendors]
        return v


# 定义课程类
class course(db.Model):
    __tablename__ = "course"
    id = Column(db.Integer,primary_key=True)
    course1 = Column(db.String(32))
    week = Column(db.String(32))
    Duration = Column(db.String(32))
    start = Column(db.Time)
    end = Column(db.Time)
    teacher = Column(db.String(32))
    place = Column(db.String(32))
    courses = db.relationship('student', secondary=courses_students, back_populates='students1')

    # 将查询对象转换成字典
    def to_dict(self):
        return {c.name: getattr(self, c.name, None) for c in self.__table__.columns}

    Base.to_dict = to_dict

    # 查询多个对象转换成字典
    def to_json(all_vendors):
        v = [ven.to_dict() for ven in all_vendors]
        return v

from datetime import date, datetime,time
class DateEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, datetime):
            return obj.strftime('%Y-%m-%d %H:%M:%S')
        elif isinstance(obj, time):
            return obj.strftime("%H-%M-%S")
        else:
            return json.JSONEncoder.default(self, obj)

# db.drop_all()
# db.create_all()
#数据库封装
def database():
    return pymysql.connect("127.0.0.1", "root", "qq2361373869", "my_god")

#注册路由
@app.route('/register', methods=['POST', 'GET'])
def register():
    if request.method == 'GET':
        return render_template('register.html')
    if request.method == 'POST':
        user = request.get_json()
        username = user['userName']
        password = user['password']
        studentno = user['studentNo']
        print(user)
        conn = database()
        cur = conn.cursor()
        sql = "insert into student(name, password, id) values(%s, %s, %s)"
        cur.execute(sql, (username, password, studentno))
        conn.commit()
        cur.close()
        conn.close()
        print(user)
        return jsonify(user)
#登录路由
@app.route('/login', methods=['POST', 'GET'])
def login():
    if session.get("user"): #验证是否登录，登录了就返回到用户页面
        return redirect('/')
    if request.method == 'GET':
        return render_template('login.html')

    if request.method == 'POST':
        user = request.get_json()
        student_id = user['userName']
        password = user['userPassword']
        user['success'] = "0"
        # user['yz'] = '1'
        db = database()
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 查询语句
        sql = "select * from student where id = %s and password = %s"
        try:
            # 执行sql语句
            cursor.execute(sql, (student_id, password))
            results = cursor.fetchall()
            print(len(results))
            if len(results) == 1:
                #session["user"] = student_id
                for i in results:
                    session['id'] = i[0]
                    session['user'] = i[1]
                user['success'] = '1'
                session['teacher'] = '0'
                print(session.get('user'))
            # 提交到数据库执行
            db.commit()
        except:
            # 如果发生错误则回滚
            traceback.print_exc()
            db.rollback()
        # 关闭数据库连接
        db.close()
        return jsonify(user)
#老师登录路由
@app.route('/loginteacher', methods=['POST', 'GET'])
def loginteacher():
    if request.method == 'POST':
        user = request.get_json()
        teacher_id = user['userName']
        password = user['userPassword']
        user['success'] = "0"
        # user['yz'] = '1'
        db = database()
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 查询语句
        sql = "select * from teacher_info where id = %s and password = %s"
        try:
            # 执行sql语句
            cursor.execute(sql, (teacher_id, password))
            results = cursor.fetchall()
            print(len(results))
            if len(results) == 1:
                for i in results:
                    session["user"] = i[1]
                    session['id'] = i[0]
                user['success'] = '1'
                session['teacher'] = '1'
                print(session.get('user'))
            # 提交到数据库执行
            db.commit()
        except:
            # 如果发生错误则回滚
            traceback.print_exc()
            db.rollback()
        # 关闭数据库连接
        db.close()
        return jsonify(user)
#退出登录
@app.route('/logout')
def logout():
    session.pop('user', None)
    return redirect('/login')
#主界面
@app.route('/', methods=['POST', 'GET'])
def hello_world():
    if session.get("user"):
        user = session.get('user')
        if session.get('teacher') == '0':
            return render_template('index.html', user=user)
        else:
            return render_template('teacher.html', user=user)
    else:
        return redirect('/login')
#选课路由
@app.route('/xuanke')
def xuanke():
    user = session.get('user')
    if session.get('teacher') == '0':
        return render_template('courseselection.html', user=user)
    else:
        return redirect('/')

#课表查询路由
@app.route('/kebiao')
def kebiao():
    user = session.get('user')
    if session.get('teacher') == '0':
        return render_template('coursetimetable.html', user=user)
    else:
        return redirect('/')

# 成绩查询路由
@app.route('/chengji')
def chengji():
    user = session.get('user')
    if session.get('teacher') == '0':
        return render_template('queryscore.html', user=user)
    else:
        return redirect('/')
#个人信息路由
@app.route('/userinformation')
def userinformation():
    user = session.get('user')
    if session.get('teacher') == '0':
        return render_template('userinformation.html', user=user)
    else:
        return redirect('/')
#修改密码路由
@app.route('/changepassword')
def changePassword():
    user = session.get('user')
    if session.get('teacher') == '0':
        return render_template('changepassword.html', user=user)
    else:
        return redirect('/')


# 修改密码路由
@app.route('/user/ChangePassword')
def ChangePassword():

    password = request.args.get('password')
    newPassword = request.args.get('newPassword')
    confirmPassword = request.args.get('confirmPassword')
    userId = session['id']
    ret = {}

    if newPassword != confirmPassword:
        ret['code'] = 1
        ret['errMsg'] = '两次密码不一致'
        return jsonify(ret)

    db = database()
    cursor = db.cursor(cursor=pymysql.cursors.DictCursor)

    sql = "SELECT password FROM teacher_info WHERE id = %s"
    cursor.execute(sql, userId)
    row = cursor.fetchone()

    if password != row['password']:
        ret['code'] = 2
        ret['errMsg'] = '旧密码不正确'
        return jsonify(ret)

    sql = "UPDATE teacher_info SET password = %s WHERE id = %s"
    cursor.execute(sql, (newPassword,userId))

    db.commit()
    cursor.close()
    db.close()

    ret['code'] = 0
    ret['errMsg'] = '成功'
    return jsonify(ret)

#教师课表路由
@app.route('/teachercoursetimetable')
def teachercoursetimetable():
    user = session.get('user')
    if session.get('teacher') == '1':
        return render_template('teacher-coursetimetable.html', user=user)
    else:
        return redirect('/')

#教师课表查询
@app.route('/teacher/List')
def List():
    id = session['id']

    ret = {}

    if id is None:
        ret['code'] = 1
        ret['errMsg'] = 'id为空'
        return jsonify(ret)

    db = database()
    cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
    sql = "SELECT DATE_FORMAT(start,'%%H:%%m:%%S') as `start`, DATE_FORMAT(`end`,'%%H:%%m:%%S') as `end`,teacher, place, `week`, Duration FROM course WHERE id = %s"
    cursor.execute(sql, id)
    row = cursor.fetchall()
    db.commit()
    cursor.close()
    db.close()

    ret['code'] = 0
    ret['errMsg'] = '成功'
    ret['data'] = row
    return jsonify(ret)

#学生成绩评价路由
@app.route('/teacherqueryscore')
def teacherqueryscore():
    user = session.get('user')
    if session.get('teacher') == '1':
        return render_template('teacher-queryscore.html', user=user)
    else:
        return redirect('/')

#学生成绩评价信息路由
@app.route('/teacher/evaluate')
def evaluate():
    id = session['id']
    ret = {}

    if id is None:
        ret['code'] = 1
        ret['errMsg'] = 'id为空'
        return jsonify(ret)

    db = database()
    cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
    sql = "SELECT `subject`.id as subject_id,chengji1,chongxiu,student.id,student.`name` AS student_name,sex,student.phone,student.email FROM teacher_info LEFT JOIN `subject` ON teacher_info.`name`=`subject`.teacher LEFT JOIN subjects_students ON `subject`.id=subjects_students.subject_id LEFT JOIN student ON subjects_students.student_id=student.id WHERE teacher_info.id=%s"
    cursor.execute(sql, id)
    row = cursor.fetchall()
    db.commit()
    cursor.close()
    db.close()

    ret['code'] = 0
    ret['errMsg'] = '成功'
    ret['data'] = row
    return jsonify(ret)


#学生成绩评价信息路由
@app.route('/teacher/evement')
def evement():
    id = request.args.get('id')
    achievement = request.args.get('achievement')
    ret = {}
    db = database()
    cursor = db.cursor()
    sql = "UPDATE `subject` SET chengji1 = %s WHERE id = %s"
    cursor.execute(sql, (achievement,id))
    db.commit()
    cursor.close()
    db.close()

    ret['code'] = 0
    ret['errMsg'] = '成功'
    return jsonify(ret)
#老师个人信息路由
@app.route('/teacheruserinformation')
def teacheruserinformation():
    user = session.get('user')
    if session.get('teacher') == '1':
        return render_template('teacher-userinformation.html', user=user)
    else:
        return redirect('/')
#老师个人信息查看路由
@app.route('/teacherinformation')
def teacherinformation():
    conn = database()
    cur = conn.cursor()
    sql = "SELECT * FROM  teacher_info where id = %s"
    id = session.get('id')
    cur.execute(sql, (id))
    u = cur.fetchall()
    cur.close()
    conn.close()
    information = []
    for row in u:
        result = {}
        result['name'] = row[1]
        result['email'] = row[2]
        result['phone'] = row[3]
        result['address'] = row[5]
        information.append(result)
    return jsonify(information)
#老师修改个人信息路由
@app.route('/changeteacherinformation', methods=['POST', 'GET'])
def changeteacherinformation():
    if request.method == 'POST':
        information = request.get_json()
        db = database()
        cur = db.cursor()
        sql = "UPDATE teacher_info  set email=%s, name=%s, phone=%s, address=%s where id=%s"
        id = session.get('id')
        name = information['name']
        email = information['email']
        phone = information['phone']
        address = information['address']
        b = (email, name, phone, address, id)
        cur.execute(sql, b)
        db.commit()
        cur.close()
        db.close()
        return jsonify(information)
#老师修改密码路由
@app.route('/teacherchangepassword')
def teacherchangepassword():
    user = session.get('user')
    if session.get('teacher') == '1':
        return render_template('teacher-changepassword.html', user=user)
    else:
        return redirect('/')

#课表的数据
@app.route('/select/')
def select():
    new = course.query.all()
    print(course.to_json(new))

    return json.dumps(course.to_json(new),cls=DateEncoder)


#课表查询的数据
@app.route('/subject_1/')
def subject_1():
    data = student.query.get(session.get('id'))
    data1 = data.students1
    print(course.to_json(data1))
    return json.dumps(course.to_json(data1),cls=DateEncoder)


# 成绩查询的数据
@app.route('/course_1/')
def course_1():
    data = student.query.get(session.get('id'))
    data1 = data.students
    print(course.to_json(data1))
    return json.dumps(course.to_json(data1),cls=DateEncoder)


#选课之后触发事件
@app.route('/select_one/')
def select_one():
    print(request.args.get('data',''))
    data = course.query.filter_by(id=request.args.get('data','')).first()
    data1= student.query.filter_by(id=session.get('id')).first()
    data3=data.to_dict()
    data4=data1.to_dict()
    data.courses.append(data1)
    db.session.add(data)
    db.session.commit()
    return '2321'
if __name__ == '__main__':
    app.run()
